set doc off
set verify off
--
--    Program: CAPGLEXT_ALLOC.sql
--    Description:
--       Extract allocation rules associated with the SOB that the user specified
--
--    Revision:
--       1st     
--    Parameter: 1. set of books short name e.g. SOB_C999

--whenever sqlerror exit failure rollback
set serveroutput on size 1000000;

declare   
   l_processed_count_b		number;
   l_rejected_count_b 		number;
   l_processed_count_f		number;
   l_rejected_count_f 		number;
   l_processed_count_l		number;
   l_rejected_count_l 		number;   
   l_err_msg				varchar2(2000);
                    		
   l_utl_file_1       		utl_file.file_type;
   l_utl_file_2       		utl_file.file_type;
   l_utl_file_3       		utl_file.file_type;
   l_filename_1       		varchar2(255);
   l_filename_2       		varchar2(255);
   l_filename_3       		varchar2(255);
   l_pathname       		varchar2(100);
                    		
   l_ngo_sob				varchar2(20);
                    		  
   cursor cur_b (p_ngo_sob varchar2) is
	select * 
	from   gl_alloc_batches 
	where  chart_of_accounts_id = (select chart_of_accounts_id 
	                               from   gl_sets_of_books 
	                               where  upper(short_name) = upper(p_ngo_sob));
	                               
   cursor cur_f (p_batch number) is
	select * 
	from   gl_alloc_formulas 
	where  allocation_batch_id = p_batch;

   cursor cur_fl (p_formula number) is
    select * 
    from   gl_alloc_formula_lines 
    where  allocation_formula_id = p_formula;


begin
   l_pathname := '/tmp';
   l_filename_1 := 'CAPGL_ALLOC_BATCH.dat';
   l_filename_2 := 'CAPGL_ALLOC_FORMULA.dat';
   l_filename_3 := 'CAPGL_ALLOC_FORMULA_LINE.dat';
   l_utl_file_1 := utl_file.fopen(l_pathname, l_filename_1, 'w');
   l_utl_file_2 := utl_file.fopen(l_pathname, l_filename_2, 'w');
   l_utl_file_3 := utl_file.fopen(l_pathname, l_filename_3, 'w');
   
   l_processed_count_b := 0;
   l_rejected_count_b  := 0;
   l_processed_count_f := 0;
   l_rejected_count_f  := 0;
   l_processed_count_l := 0;
   l_rejected_count_l  := 0;
   
   l_ngo_sob := '&1';
       
   for b in cur_b(l_ngo_sob) loop

     utl_file.put_line( l_utl_file_1, 
       b.allocation_batch_id|| chr(9) ||
       b.name|| chr(9) ||
       b.chart_of_accounts_id|| chr(9) ||
       b.validation_status|| chr(9) ||
       b.actual_flag|| chr(9) ||
       to_char(b.last_update_date, 'DD-MON-YYYY HH24:MI:SS')|| chr(9) ||
       b.last_updated_by|| chr(9) ||
       to_char(b.creation_date, 'DD-MON-YYYY HH24:MI:SS')|| chr(9) ||
       b.created_by|| chr(9) ||
       b.last_update_login|| chr(9) ||
       b.validation_request_id|| chr(9) ||
       b.description|| chr(9) ||
       CAPBREXT_COMMON.GetUser(b.last_updated_by)|| chr(9) ||
       CAPBREXT_COMMON.GetUser(b.created_by)|| chr(9) ||
       CAPBREXT_COMMON.GetFlexStrCode(b.chart_of_accounts_id)
     );
     
     l_processed_count_b := l_processed_count_b + 1;
     
     for f in cur_f(b.allocation_batch_id) loop         
       
       utl_file.put_line( l_utl_file_2, 
         f.allocation_formula_id|| chr(9) ||
         f.allocation_batch_id|| chr(9) ||
         f.name|| chr(9) ||
         f.run_sequence|| chr(9) ||
         f.je_category_name|| chr(9) ||
         f.full_allocation_flag|| chr(9) ||
         f.validation_status|| chr(9) ||
         to_char(f.last_update_date, 'DD-MON-YYYY HH24:MI:SS')|| chr(9) ||
         f.last_updated_by|| chr(9) ||
         to_char(f.creation_date, 'DD-MON-YYYY HH24:MI:SS')|| chr(9) ||
         f.created_by|| chr(9) ||
         f.last_update_login|| chr(9) ||
         f.description|| chr(9) ||
         CAPBREXT_COMMON.GetUser(f.last_updated_by)|| chr(9) ||
         CAPBREXT_COMMON.GetUser(f.created_by)
         );
       
       l_processed_count_f := l_processed_count_f + 1;
       
       for l in cur_fl(f.allocation_formula_id) loop             
         
         utl_file.put_line( l_utl_file_3, 
           l.allocation_formula_id|| chr(9) ||
           l.line_number|| chr(9) ||
           l.line_type|| chr(9) ||
           l.operator|| chr(9) ||
           to_char(l.last_update_date, 'DD-MON-YYYY HH24:MI:SS')|| chr(9) ||
           l.last_updated_by|| chr(9) ||
           to_char(l.creation_date, 'DD-MON-YYYY HH24:MI:SS')|| chr(9) ||
           l.created_by|| chr(9) ||
           l.last_update_login|| chr(9) ||
           l.amount|| chr(9) ||
           l.relative_period|| chr(9) ||
           l.period_name|| chr(9) ||
           l.currency_code|| chr(9) ||
           l.transaction_currency|| chr(9) ||
           l.actual_flag|| chr(9) ||
           l.budget_version_id|| chr(9) ||
           l.encumbrance_type_id|| chr(9) ||
           l.amount_type|| chr(9) ||
           l.segment_types_key|| chr(9) ||
           l.segment_break_key|| chr(9) ||
           l.segment1|| chr(9) ||
           l.segment2|| chr(9) ||
           l.segment3|| chr(9) ||
           l.segment4|| chr(9) ||
           l.segment5|| chr(9) ||
           l.segment6|| chr(9) ||
           l.segment7|| chr(9) ||
           l.segment8|| chr(9) ||
           l.segment9|| chr(9) ||
           l.segment10|| chr(9) ||
           l.segment11|| chr(9) ||
           l.segment12|| chr(9) ||
           l.segment13|| chr(9) ||
           l.segment14|| chr(9) ||
           l.segment15|| chr(9) ||
           l.segment16|| chr(9) ||
           l.segment17|| chr(9) ||
           l.segment18|| chr(9) ||
           l.segment19|| chr(9) ||
           l.segment20|| chr(9) ||
           l.segment21|| chr(9) ||
           l.segment22|| chr(9) ||
           l.segment23|| chr(9) ||
           l.segment24|| chr(9) ||
           l.segment25|| chr(9) ||
           l.segment26|| chr(9) ||
           l.segment27|| chr(9) ||
           l.segment28|| chr(9) ||
           l.segment29|| chr(9) ||
           l.segment30|| chr(9) ||
           CAPBREXT_COMMON.GetUser(l.last_updated_by)|| chr(9) ||
           CAPBREXT_COMMON.GetUser(l.created_by)|| chr(9) ||
           CAPBREXT_COMMON.GetBudgetName(l.budget_version_id)|| chr(9) ||
           CAPBREXT_COMMON.GetEncumType(l.encumbrance_type_id)
           );
         
         l_processed_count_l := l_processed_count_l + 1;        
       end loop;

     end loop;
     
   end loop;       
   
   if l_processed_count_b = 0 then
     l_rejected_count_b := l_rejected_count_b + 1;
     l_err_msg := 'No allocation rules defined for this ngo '||l_ngo_sob;
   end if;
      
   -- insert into log table
   insert into capcnv_log ( prog_id, process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPGLEXT_ALLOC', sysdate, l_processed_count_b, l_rejected_count_b, l_processed_count_b-l_rejected_count_b, l_err_msg );
   
   l_err_msg := '';
   if l_processed_count_f = 0 then
     l_rejected_count_f := l_rejected_count_f + 1;
     l_err_msg := 'No allocation formulas defined for this ngo '||l_ngo_sob;
   end if;
        
   insert into capcnv_log ( prog_id, process_date, record_processed,record_rejected,record_extracted,err_msg) 
   values ('CAPGLEXT_ALLOC', sysdate, l_processed_count_f, l_rejected_count_f, l_processed_count_f-l_rejected_count_f, l_err_msg );        

   l_err_msg := '';    
   if l_processed_count_l = 0 then
     l_rejected_count_l := l_rejected_count_l + 1;
     l_err_msg := 'No allocation formula lines defined for this ngo '||l_ngo_sob;
   end if;
   
   insert into capcnv_log ( prog_id, process_date, record_processed,record_rejected,record_extracted,err_msg) 
   values ('CAPGLEXT_ALLOC', sysdate, l_processed_count_l, l_rejected_count_l, l_processed_count_l-l_rejected_count_l, l_err_msg );
      
   commit;

   utl_file.fclose(l_utl_file_1);
   utl_file.fclose(l_utl_file_2);
   utl_file.fclose(l_utl_file_3);
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;
end;
/

--exit
